go

exec dbo.p_drop_proc_if_exists 'p_check_reference_value';

go

--Function of checking reference value
create procedure p_check_reference_value(
    --Column id
    @a_column_id binary(16),
    --Reference value
    @a_ref_value binary(16))
with execute as owner
as
begin
	declare
	--Ref table name
    @a_ref_table_name varchar(26),
    --Row count
    @a_row_count numeric;
     
	--If value is null there is no need for any check
	if @a_ref_value is null
		return;
		
	declare
	--Ref table cursor
	@a_ref_cursor cursor,
	--Ref table id
	@a_ref_table_id binary(16);
	
	set @a_ref_cursor = cursor fast_forward read_only for
	select ref_table_id from meta_ref_targets
	where column_id = @a_column_id;
	
	open @a_ref_cursor;
	fetch next from @a_ref_cursor into @a_ref_table_id;

	--Enumerating all tables that the column references 
	while @@fetch_status = 0
	begin 
		--Reading ref table name
		select @a_ref_table_name = table_name
		from meta_tables
		where table_id = @a_ref_table_id;
	 
		--Searching for referenced row
		declare @ref_search_sql nvarchar(500);
		set @ref_search_sql = 'select @row_count_out = count(*) from tbl_' + @a_ref_table_name
		+ ' where id = @row_id';

		exec sp_executesql @ref_search_sql,
		N'@row_id binary(16), @row_count_out int output', 
		@row_id = @a_ref_value,
		@row_count_out = @a_row_count output;
	 
		--If referenced row was found
		if @a_row_count > 0
			return;
			
		fetch next from @a_ref_cursor into @a_ref_table_id;       
	end;
	
	close @a_ref_cursor;
	deallocate @a_ref_cursor;
	
	declare
	--Ref value str
    @a_ref_value_str varchar(32),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    set @a_ref_value_str = dbo.p_bin_to_str(@a_ref_value);

	--Referenced row was not found
	raiserror('A row that was referenced by the column was not found (column_id = %s, ref_value = %s) (#error_code = 20024)',
			16, 1, @a_column_id_str, @a_ref_value_str);
	rollback;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_ref_checking';

go

--Function of creating check ref value trigger
create procedure p_create_ref_checking(
    --Column id
    @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Table name
    @a_table_name varchar(26),
    --Trigger name
    @a_trigger_name varchar(33),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_column_name = column_name, @a_table_id = table_id
    from meta_ref_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
 
    --Creating trigger of checking column value
    set @a_trigger_name = 'r' + dbo.p_bin_to_str(@a_column_id);
    
    exec ('create trigger ' + @a_trigger_name
    + ' on tbl_' + @a_table_name
    + ' after insert, update as'
    + ' begin'
    + ' declare @ref_value binary(16);'
    + ' declare @ref_cursor cursor;'
    + ' set @ref_cursor = cursor fast_forward read_only for select '
    + @a_column_name + ' from inserted;'
    + ' open @ref_cursor;'
    + ' fetch next from @ref_cursor into @ref_value;'
    + ' while @@fetch_status = 0'
    + ' begin'
    + ' exec dbo.p_check_reference_value 0x'
    + @a_column_id_str + ', @ref_value;'
    + ' fetch next from @ref_cursor into @ref_value;'
    + ' end;'
    + ' close @ref_cursor;'
    + ' deallocate @ref_cursor;'
    + ' end;');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_ref_checking';

go

--Function of dropping check ref value trigger
create procedure p_drop_ref_checking(
    --Column id
    @a_column_id binary(16))
as
begin
	--Column id str
	declare @a_column_id_str varchar(32);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
    exec ('drop trigger ' + 'r' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_ref_column';

go

--Function of adding reference column
create procedure meta_add_ref_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);    
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
     
    --Changing the metadata
    insert into meta_ref_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    --Creating new column
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' binary(16)');
         
    --Creating trigger of checking column value
    exec dbo.p_create_ref_checking @a_column_id;
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_ref_default';

go

--Function of setting column default value
create procedure meta_set_ref_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32),
    --Default value str
    @a_default_value_str varchar(32),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value binary(16);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
    if @a_default_value is not null
		set @a_default_value_str = dbo.p_bin_to_str(@a_default_value);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_default_value = default_value
    from meta_ref_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_ref_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
     if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default 0x' + @a_default_value_str
        + ' for ' + @a_column_name);
    else
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_ref_target';

go

--Function of adding ref target
create procedure meta_add_ref_target(
    --Column id
    @a_column_id binary(16),
    --Ref table id
    @a_ref_table_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32),
    --Ref table id str
    @a_ref_table_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
    select @a_row_count = count(*)
    from meta_ref_targets
    where column_id = @a_column_id and ref_table_id = @a_ref_table_id;
    
    if @a_row_count > 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		set @a_ref_table_id_str = dbo.p_bin_to_str(@a_ref_table_id);
		
        raiserror('Adding duplicate reference target is forbidden (column_id = %s, ref_table_id = %s) (#error_code = 20022)',
        16, 1, @a_column_id_str, @a_ref_table_id_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;
     
    insert into meta_ref_targets (column_id, ref_table_id)
    values (@a_column_id, @a_ref_table_id);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_ref_target';

go

--Function of removing ref target
create procedure meta_delete_ref_target(
    --Column id
    @a_column_id binary(16),
    --Ref table id
    @a_ref_table_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Ref column name
    @a_ref_column_name varchar(26),
    --Ref table name
    @a_ref_table_name varchar(26),
    --Row count
    @a_row_count numeric;
    
    --Searching for the target
    select @a_row_count = count(*)
    from meta_ref_targets
    where column_id = @a_column_id
    and ref_table_id = @a_ref_table_id;
    
    if @a_row_count = 0
    begin
		declare @a_column_id_str varchar(32),
		@a_ref_table_id_str varchar(32);
		
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		set @a_ref_table_id_str = dbo.p_bin_to_str(@a_ref_table_id);
		
        raiserror('Deleting nonexistent reference target is forbidden (column_id = %s, ref_table_id = %s) (#error_code = 20023)',
        16, 1, @a_column_id_str, @a_ref_table_id_str);
        return;
    end;

    --Reading ref column properties
    select @a_ref_column_name = column_name,
    @a_table_id = table_id
    from meta_ref_columns
    where column_id = @a_column_id;
    
    set xact_abort on;
    begin tran;
        
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);    

    exec ('update tbl_'
    + @a_table_name
    + ' set ' + @a_ref_column_name + ' = null'
    + ' where exists('
    + 'select id from tbl_' + @a_ref_table_name
    + ' as ref where ref.id = tbl_' + @a_table_name + '.' + @a_ref_column_name + ')');

    delete from meta_ref_targets
    where column_id = @a_column_id
    and ref_table_id = @a_ref_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_ref_col_constrs';

go

--Function of creating all ref constraints
create procedure p_create_ref_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	--Creating trigger of checking column value
    exec dbo.p_create_ref_checking @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_ref_col_constrs';

go

--Function of droppping all ref constraints
create procedure p_drop_ref_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	--Dropping trigger of checking column value
    exec dbo.p_drop_ref_checking @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_ref_checking';

go

-- Function of setting the need to check reference values
create procedure meta_set_ref_checking(
	-- Column id
	@a_column_id binary(16),
	-- The need to check reference values
	@a_ref_checking numeric(1))
with execute as owner
as
begin
    set nocount on;
	declare
	--Result
	@a_result numeric(1),
	--Table name
	@a_table_name varchar(26),
    --Trigger name
    @a_trigger_name varchar(33);

	exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
	--The trigger name    
    set @a_trigger_name = 'r' + dbo.p_bin_to_str(@a_column_id);
    
    --The table name
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
   
    set xact_abort on;
    begin tran;
    
    if @a_ref_checking = 1
		exec ('enable trigger ' + @a_trigger_name + ' on tbl_' + @a_table_name);
	else
		exec ('disable trigger ' + @a_trigger_name + ' on tbl_' + @a_table_name);
    
    --Updating the metadata
    update meta_ref_columns
    set ref_checking = @a_ref_checking 
    where column_id = @a_column_id;
    
    commit;
end;